﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using Ivy.Common.Framework.Base;
using Ivy.Common.Framework.DataAccess;
using Ivy.UserRegistration.Entity;

namespace Ivy.UserRegistration.DataAccess
{
    public class UserRoleDao : IDaoInterface<UserRole, UserRoleCriteria>

    {
        #region  Variable & constant
        #endregion  Variable & constant

        #region Constructor & Destructor
        public UserRoleDao() : base() { }
        #endregion Constructor & Destructor

        #region Properties
        #endregion Properties

        #region Public Methods
        #region  IDaoInterface<UserRole, UserRolesCriteria> Members
        /// <summary>
        /// The is to insert a record in UserRole table
        /// </summary>
        /// <param name="UserRoleEntity">an instance of UserRole</param>
        /// <returns>A flag to indicate successful insert</returns>
        public bool Insert(UserRole UserRoleEntity)
        {
            bool IsOperationSuccessful = false;
            try
            {
                if (UserRoleEntity.EntityState == Common.Framework.Base.EntityState.New)
                {
                    DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                    DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                    CmdObj.CommandText = "Ivy_UserRole_Insert";
                    //CmdObj.CommandText = UserManagementSQLCommandText.InsertUser();
                    CmdObj.CommandType = CommandType.StoredProcedure;
                    //CmdObj.CommandType = CommandType.Text;

                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserKey", ParameterDirection.Input, DbType.Int32, UserRoleEntity.UserKey);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserSocietyKey", ParameterDirection.Input, DbType.Int32, UserRoleEntity.UserSocietyKey);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserRoleKey", ParameterDirection.Input, DbType.Int32, UserRoleEntity.UserRoleKey);
                    //DataAccessProviderUtility.AddParameter(CmdObj, "@UserLoginID", ParameterDirection.Input, DbType.String, UserRoleEntity.UserLoginID);
                    //DataAccessProviderUtility.AddParameter(CmdObj, "@UserRoleCode", ParameterDirection.Input, DbType.String, UserRoleEntity.UserRoleCode);

                    DatabaseCommon.InsertStoredProcForParameterDatEntityBase(CmdObj, UserRoleEntity);

                    DataAccessProviderUtility.AddParameter(CmdObj, "@Sys_DateOfCreation", ParameterDirection.Output, DbType.DateTime, DBNull.Value);

                    CmdObj.Connection = DbConn;
                    DbConn.Open();
                    CmdObj.Prepare();
                    int _NoOfRecords = CmdObj.ExecuteNonQuery();
                    if (_NoOfRecords == 1)
                    {
                        UserRoleEntity.UserKey = Convert.ToInt32(CmdObj.Parameters["@UserKey"].Value);
                        UserRoleEntity.Sys_DateOfCreation = Convert.ToDateTime(CmdObj.Parameters["@Sys_DateOfCreation"].Value);
                        UserRoleEntity.Sys_DateOfModification = UserRoleEntity.Sys_DateOfCreation;
                        UserRoleEntity.EntityState = EntityState.UnChanged;
                        IsOperationSuccessful = true;
                    }
                    CmdObj.Dispose();
                    DbConn.Close();
                }
                else
                {
                    throw new Exception("For inserting new User entity data to database, [EntityState] attribute should be [New]");
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
            return IsOperationSuccessful;
        }

        public bool Update(UserRole UserRoleEntity)
        {
            bool IsOperationSuccessful = false;
            try
            {
                if (UserRoleEntity.EntityState == Common.Framework.Base.EntityState.Changed)
                {
                    DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                    DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                    CmdObj.CommandText = "Ivy_UserRole_Update";
                    CmdObj.CommandType = CommandType.StoredProcedure;

                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserKey", ParameterDirection.Input, DbType.Int32, UserRoleEntity.UserKey);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserSocietyKey", ParameterDirection.Input, DbType.Int32, UserRoleEntity.UserSocietyKey);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserRoleKey", ParameterDirection.Input, DbType.Int32, UserRoleEntity.UserRoleKey);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserLoginID", ParameterDirection.Input, DbType.String, UserRoleEntity.UserLoginID);
                    DataAccessProviderUtility.AddParameter(CmdObj, "@UserRoleCode", ParameterDirection.Input, DbType.String, UserRoleEntity.UserRoleCode);


                    DatabaseCommon.UpdateStoredProcForParameterDatEntityBase(CmdObj, UserRoleEntity);

                    DataAccessProviderUtility.AddParameter(CmdObj, "@Sys_DateOfModification", ParameterDirection.Output, DbType.DateTime, DBNull.Value);

                    CmdObj.Connection = DbConn;
                    DbConn.Open();
                    CmdObj.Prepare();
                    int _NoOfRecords = CmdObj.ExecuteNonQuery();
                    if (_NoOfRecords == 1)
                    {
                        UserRoleEntity.Sys_DateOfModification = Convert.ToDateTime(CmdObj.Parameters["@Sys_DateOfModification"].Value);
                        UserRoleEntity.EntityState = EntityState.UnChanged;
                        IsOperationSuccessful = true;
                    }

                    CmdObj.Dispose();
                    DbConn.Close();
                }
                else
                {
                    throw new Exception("For Updating existing User entity data to database, [EntityState] attribute should be [Changed]");
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
            return IsOperationSuccessful;
        }

        public bool Delete(UserRole UserRoleEntity)
        {
            bool IsOperationSuccessful = false;
            try
            {
                UserRoleEntity.EntityState = EntityState.Changed;
                UserRoleEntity.Sys_IsDeleted = true;
                if (Update(UserRoleEntity))
                {
                    IsOperationSuccessful = true;
                }


            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
            return IsOperationSuccessful;
        }

        public UserRole GetByID(string ID)
        {
            throw new NotSupportedException();
        }

        public List<UserRole> GetAll()
        {
            throw new NotImplementedException();
        }

        public List<UserRole> GetByCriteria(UserRoleCriteria UserRoleCriteria)
        {
            throw new NotImplementedException();
        }
        #endregion
        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="UserKey"></param>
        /// <param name="SocietyKey"></param>
        /// <param name="RoleKey"></param>
        /// <returns></returns>
        public UserRole GetByUserKeySocietyKeyRoleKey(int UserKey, int SocietyKey, int RoleKey)
        {
            UserRole _SelectedUser = null; //IMPORTANT NOTE:- Just declare the variable never create an instance, create instance only when required.
            try
            {
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "Ivy_UserRole_GetByUserKeySocietyKeyRoleKey";
                CmdObj.CommandType = CommandType.StoredProcedure;

                DataAccessProviderUtility.AddParameter(CmdObj, "@UserKey", ParameterDirection.Input, DbType.Int32, Convert.ToInt32(UserKey));
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserSocietyKey", ParameterDirection.Input, DbType.Int32, Convert.ToInt32(SocietyKey));
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserRoleKey", ParameterDirection.Input, DbType.Int32, Convert.ToInt32(RoleKey));

                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();
                DbDataReader _DataReader = CmdObj.ExecuteReader();

                if (_DataReader.HasRows)
                {
                    if (_DataReader.Read())
                    {
                        _SelectedUser = new UserRole(); //IMPORTANT NOTE:- Create instance only when required.
                        TranslateUserFieldsFromReaderToEntity(_SelectedUser, _DataReader);
                    }
                }
                else
                {
                    throw new Exception("No UserRole found");
                }
                CmdObj.Dispose();
                DbConn.Close();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
            return _SelectedUser;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="UserKey"></param>
        /// <param name="SocietyKey"></param>
        /// <returns></returns>
        public List<UserRole> GetByUserKeySocietyKey(int UserKey, int SocietyKey)
        {
            List<UserRole> _UserRoleList = new List<UserRole>();
            try
            {
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandType = CommandType.StoredProcedure;
                CmdObj.CommandText = "Ivy_UserRole_GetByUserKeySocietyKey";


                DataAccessProviderUtility.AddParameter(CmdObj, "@UserKey", ParameterDirection.Input, DbType.Int32, Convert.ToInt32(UserKey));
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserSocietyKey", ParameterDirection.Input, DbType.Int32, Convert.ToInt32(SocietyKey));

                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();

                DbDataReader _DataReader = CmdObj.ExecuteReader();
                if (_DataReader.HasRows)
                {
                    while (_DataReader.Read())
                    {
                        UserRole _TempUserRole = new UserRole(); //IMPORTANT NOTE:- Create instance only when required.
                        this.TranslateUserFieldsFromReaderToEntity(_TempUserRole, _DataReader);
                        _UserRoleList.Add(_TempUserRole);
                    }
                }
                CmdObj.Dispose();
                DbConn.Close();


            }
            catch(Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
            return _UserRoleList;
        }
       
        /// <summary>
        /// 
        /// </summary>
        /// <param name="UserKey"></param>
        /// <param name="RoleKey"></param>
        /// <returns></returns>
        public List<UserRole> GetByUserKeyRoleKey(int UserKey, int RoleKey)
        {
            List<UserRole> _UserRoleList = new List<UserRole>();
            try
            {
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandType = CommandType.StoredProcedure;
                CmdObj.CommandText = "Ivy_UserRole_GetByUserKeyRoleKey";

                DataAccessProviderUtility.AddParameter(CmdObj, "@UserKey", ParameterDirection.Input, DbType.Int32, Convert.ToInt32(UserKey));
                DataAccessProviderUtility.AddParameter(CmdObj, "@UserRoleKey", ParameterDirection.Input, DbType.Int32, Convert.ToInt32(RoleKey));
                

                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();

                DbDataReader _DataReader = CmdObj.ExecuteReader();
                if (_DataReader.HasRows)
                {
                    while (_DataReader.Read())
                    {
                        UserRole _TempUserRole = new UserRole(); //IMPORTANT NOTE:- Create instance only when required.
                        this.TranslateUserFieldsFromReaderToEntity(_TempUserRole, _DataReader);
                        _UserRoleList.Add(_TempUserRole);
                    }
                }
                CmdObj.Dispose();
                DbConn.Close();


            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
            return _UserRoleList;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="UserKey"></param>
        /// <returns></returns>
        public List<UserRole> GetByUserKey(int UserKey)
        {
            List<UserRole> _UserRoleList = new List<UserRole>();
            try
            {
                DbConnection DbConn = DataAccessProviderUtility.CreateConnectionWithConnectionString();
                DbCommand CmdObj = DataAccessProviderUtility.CreateCommand();
                CmdObj.CommandText = "Ivy_UserRole_GetByUserKey";
                CmdObj.CommandType = CommandType.StoredProcedure;

                DataAccessProviderUtility.AddParameter(CmdObj, "@UserKey", ParameterDirection.Input, DbType.Int32, UserKey);
                
                CmdObj.Connection = DbConn;
                DbConn.Open();
                CmdObj.Prepare();

                DbDataReader _DataReader = CmdObj.ExecuteReader();
                if (_DataReader.HasRows)
                {
                    while (_DataReader.Read())
                    {
                        UserRole _TempUserRole = new UserRole(); //IMPORTANT NOTE:- Create instance only when required.
                        this.TranslateUserFieldsFromReaderToEntity(_TempUserRole, _DataReader);
                        _UserRoleList.Add(_TempUserRole);
                    }
                }
                CmdObj.Dispose();
                DbConn.Close();
                
                    
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
           return _UserRoleList; 
        }


        #endregion Public Methods

        #region Events
        #endregion Events

        #region Private Methods
        private void TranslateUserFieldsFromReaderToEntity(UserRole pUserRole, DbDataReader pDbDataReader)
        {
            try
            {
                pUserRole.UserKey = Convert.ToInt32(pDbDataReader["UserKey"]);
                pUserRole.UserSocietyKey = Convert.ToInt32(pDbDataReader["UserSocietyKey"]);
                pUserRole.UserRoleKey = Convert.ToInt32(pDbDataReader["UserRoleKey"]);
                pUserRole.UserLoginID = Convert.ToString(pDbDataReader["UserLoginID"]);
                pUserRole.UserRoleCode = Convert.ToString(pDbDataReader["UserRoleCode"]);
                

                DatabaseCommon.TranslateSystemFieldsFromReaderToEntity(pUserRole, pDbDataReader);
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
                throw ex;
            }
        }
        #endregion Private Methods
    }
}
